/**
 * Copyright (c) 2015-2017, Henry Yang 杨勇 (gismail@foxmail.com).
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.lambkit.db.mgr;

import cn.hutool.core.util.StrUtil;
import com.lambkit.core.Attr;
import com.lambkit.db.IRowData;
import com.lambkit.db.RowData;
import com.lambkit.db.sql.SqlBuilder;
import com.lambkit.util.Printer;

import java.util.ArrayList;
import java.util.List;


public class PivotBuilder {

	public Pivot build(MgrTable tbc, String row, String column, String measure, String prefix) {
		Pivot pivot = new Pivot();
		pivot.setPrefix(prefix);
		setColumn(pivot, tbc, column, prefix);
		setRow(pivot, tbc, row, prefix);
		setMeasure(pivot, tbc, measure, prefix);
		int collen = StrUtil.isNotBlank(column) ? column.split(",").length : 0;
		pivot.getTable().setRowlen(collen+1);
		return pivot;
	}

	/*
	public String getSelectSql() {
		String selectSql = "";
		if(StrUtil.isNotBlank(rowSql)) selectSql += " " + rowSql;
		if(StrUtil.isNotBlank(measureSql)) selectSql += "," + measureSql;
		selectSql += " ";
		return selectSql;
	}
	*/
	/**
	 * 加入where过滤条件
	 * @param tbc
	 * @param whereSql
	 * @param sqlParas
	 */
	public void setSql(Pivot pivot, MgrTable tbc, String whereSql, Object[] sqlParas) {
		pivot.setWhereSql(whereSql);
		pivot.setSqlParas(sqlParas);
		String column = pivot.getColumnSql();
		SqlBuilder sb = new SqlBuilder();
		String sql = sb.append("select ").append(column).appendFrom(tbc)
		.append(pivot.getWhereSql()).append(" group by ").append(column)
		.append(" order by ").append(column).append(" desc").build();
		// Printer.print(this, "db"("getColumn SQL="+sql);
		List<RowData> list = tbc.db().find(sql, pivot.getSqlParas());
		List<IField> mns = pivot.getTable().getMeasures();
		List<Attr> colHead = new ArrayList<>();
		for (int m = 0; m < list.size(); m++) {
			for(int j=0; j<mns.size(); j++) {
				Attr nrd = Attr.of(list.get(m).toMap());
				nrd.set(mns.get(j).getName(), mns.get(j).getTitle());
				colHead.add(nrd);
			}
		}
		pivot.getTable().setColHead(colHead);
		//
		setRowCategory(pivot, tbc);
		setColCategory(pivot, tbc);
		setData(pivot, tbc);
		//over
	}
	
	private void setRowCategory(Pivot pivot, MgrTable tbc) {
		String row = pivot.getRowSql();
		SqlBuilder sb = new SqlBuilder();
		String sql = sb.append("select ").append(row).appendFrom(tbc)
		.append(" group by ").append(row)
		.append(" order by ").append(row).append(" desc").build();
		// Printer.print(this, "db"("getColumn SQL="+sql);
		List<RowData> list = tbc.db().find(sql);
		List<Attr> rows = new ArrayList<>();
		for (int m = 0; m < list.size(); m++) {
			rows.add(Attr.of(list.get(m).toMap()));
		}
		pivot.getTable().setRowCategory(rows);
	}

	private void setColCategory(Pivot pivot, MgrTable tbc) {
		String column = pivot.getColumnSql();
		if(StrUtil.isBlank(column)) {
			return;
		}
		SqlBuilder sb = new SqlBuilder();
		String sql = sb.append("select ").append(column).appendFrom(tbc)
		.append(" group by ").append(column)
		.append(" order by ").append(column).append(" desc").build();
		// Printer.print(this, "db"("getColumn SQL="+sql);
		List<RowData> list = tbc.db().find(sql);
		List<IField> mns = pivot.getTable().getMeasures();
		List<Attr> cols = new ArrayList<>();
		sb.clear();
		for (int m = 0; m < list.size(); m++) {
			for(int j=0; j<mns.size(); j++) {
				Attr nrd = Attr.of(list.get(m).toMap());
				IField fld = mns.get(j);
				nrd.set(fld.getName(), fld.getTitle());
				cols.add(nrd);
				sb.append(", ").append(getMeasureSelectSql(pivot, nrd, fld, m));
			}
		}
		String selectSql = sb.build();
		String measureSql = pivot.getMeasureSql();
		measureSql = StrUtil.isNotBlank(selectSql) ? selectSql.substring(1) : measureSql;
		pivot.setMeasureSql(measureSql);
		pivot.getTable().setColCategory(cols);
	}

	private void setData(Pivot pivot, MgrTable tbc) {
		String row = pivot.getRowSql();
		SqlBuilder sb = new SqlBuilder();
		String sql = sb.append("select ").append(pivot.getRowSql()).append(", ").append(pivot.getMeasureSql()).appendFrom(tbc)
				.append(pivot.getWhereSql()).append(" group by ").append(row)
				.append(" order by ").append(row).append(" desc").build();
		// Printer.print(this, "db"("getColumn SQL="+sql);
		List<RowData> list = tbc.db().find(sql, pivot.getSqlParas());
		List<Attr> datas = new ArrayList<>();
		for (int m = 0; m < list.size(); m++) {
			datas.add(Attr.of(list.get(m).toMap()));
		}
		pivot.getTable().setData(datas);
	}
	
	private String getMeasureSelectSql(Pivot pivot, Attr col, IField ms, int n) {
		String sql = "";
		//sum(case when year='2008' and water_system='长江' then pH else null end) as pH
		SqlBuilder sb = new SqlBuilder();
		sb.append(ms.getStr("lk0pt1ysf")).append("(case when ");
		List<IField> columns = pivot.getTable().getColumns();
		for(int i=0; i<columns.size(); i++) {
			IField fld = columns.get(i);
			String name = fld.getName();
			if(i>0) {
				sb.append(" and ");
			}
			sb.append(name).append("=");
			String value = col.get(name);
			String measure_type = fld.getDatatype();
			if(measure_type.contains("varchar") || measure_type.contains("date")) {
				sb.append("'").append(value).append("'");
			} else {
				sb.append(value);
			}
		}
		sb.append(" then ").append(ms.getName()).append(" else null end) as ");
		String extname = ms.getName() + "_" + n;
		sb.append(extname);
		sql = sb.build();
		Printer.print(this, "db", "----"+sql);
		return sql;
	}
	
	/**
	 * 设置行
	 * @param tbc
	 * @param row
	 * @param prefix
	 * @return
	 */
	private PivotBuilder setRow(Pivot pivot, MgrTable tbc, String row, String prefix) {
		if(!StrUtil.isNotBlank(row)) {
			return this;
		}
		String theSql = "";
		String[] rows = row.split(",");
		for (int i = 0; i < rows.length; i++) {
			String ss=  rows[i].trim();
			if(StrUtil.isNotBlank(ss)) {
				theSql += "," + prefix + ss;
				pivot.getTable().addRow(getField(tbc, ss));
			}
		}
		pivot.setRowSql(theSql.substring(1));
		return this;
	}
	
	/**
	 * 设置列
	 * @param tbc
	 * @param column
	 * @param prefix
	 * @return
	 */
	private PivotBuilder setColumn(Pivot pivot, MgrTable tbc, String column, String prefix) {
		if(!StrUtil.isNotBlank(column)) {
			return this;
		}
		String theSql = "";
		String[] columns = column.split(",");
		for (int i = 0; i < columns.length; i++) {
			String ss=  columns[i].trim();
			if(StrUtil.isNotBlank(ss)) {
				theSql += "," + prefix + ss;
				pivot.getTable().addColumn(getField(tbc, ss));
			}
		}
		pivot.setColumnSql(theSql.substring(1));
		return this;
	}
	/**
	 * 设置度量
	 * @param tbc table
	 * @param measure COUNT(value)
	 * @param prefix
	 * @return
	 */
	private PivotBuilder setMeasure(Pivot pivot, MgrTable tbc, String measure, String prefix) {
		if(!StrUtil.isNotBlank(measure)) {
			return this;
		}
		String theSql = "";
		String[] sers = measure.split(",");
		for (int i = 0; i < sers.length; i++) {
			String ss=  sers[i].trim();
			if(!StrUtil.isNotBlank(ss)) {
				continue;
			}
			int fst = ss.indexOf("(");
			if(fst < 1) {
				continue;
			}
			String yun = ss.substring(0, fst).trim();
			String themeasure = ss.substring(fst + 1, ss.indexOf(")")).trim();
			String ssSql = getOneMeasure(tbc, themeasure, yun, prefix);
			if(StrUtil.isNotBlank(ssSql)) {
				theSql += "," + ssSql;
				IField fld = getField(tbc, themeasure);
				fld.putAttr("lk0pt1ysf", yun);
				pivot.getTable().addMeasure(fld);
			}
		}
		pivot.setMeasureSql(theSql.substring(1));
		return this;
	}

	/**
	 * 获取单个序列的Sql
	 * @param measure
	 * @param yuns
	 * @param prefix
	 * @return
	 */
	private String getOneMeasure(MgrTable tbc, String measure, String yuns, String prefix) {
		measure = measure.trim();
		IField fld = null;
		String measure_type = null;
		if(tbc!=null && tbc.getModel()!=null && tbc.getModel().getId()!=null) {
			fld = tbc.getField(measure);
			if(fld != null) {
				measure_type = fld.getDatatype();
			}
		}
		if(StrUtil.isBlank(measure_type)) {
			return null;
		}
		if(fld!=null && fld.getIskey().equals("Y")) {
			yuns = "COUNT";
		} else if(measure_type.endsWith("Integer") ||
			measure_type.endsWith("Long") ||
			measure_type.endsWith("Float") ||
			measure_type.endsWith("Double") || 
			measure_type.startsWith("int") ||
			measure_type.startsWith("long") ||
			measure_type.startsWith("float") ||
			measure_type.startsWith("double") ||
			measure_type.startsWith("number") ||
			measure_type.startsWith("numeric")) {
			if(yuns.equalsIgnoreCase("COUNT")) {
				yuns = "SUM";
			}
		} else {
			yuns = "COUNT";
		}
		return measure = yuns + "(" + prefix + measure + ") as " + measure + "_" + yuns.toLowerCase();
	}
	/*
	private String getFieldName(TableConfig tbc, String fldname) {
		IField fldm = getField(tbc, fldname);
		fldname = fldm==null ? fldname : fldm.getShowName();
		return fldname;
	}
	*/
	private IField getField(MgrTable tbc, String fldname) {
		return tbc.getField(fldname);
	}
}
